package packageGenerateData;

import packageInputData.DbConnection;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class GenerateDataPTU {


	/**
	 * function 
	 * to generate PTU data and put it into the database
	 * @return			void
	*/	
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void generatePTU()
	{
		
		String siteName="";
		List 	list1 = new ArrayList(),
				list2 = new ArrayList();
		
		String req = "";
		int inserts=0,rinexDoy=0,rinexHour=0;
		Timestamp time;
		
		//connect to the databse
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
	  	
		//get the date, the year and the doy from table "param"	  	
		int paramDoy= (Integer)(DbConnection.getFromDB("doy", "param"));
		Date d=(Date)DbConnection.getFromDB("date", "param");
		int paramYear = d.getYear()+1900;
		
		//get GPS code list from sites_gps in list1 then in data[]
		try {
		       list1 =(List) qRunner.query(conn,
							    		   "select code from sites_gps",
							    		   new ArrayListHandler());
		} catch (SQLException e2) {
		       e2.printStackTrace();
		}
	
		for(int j=0;j<list1.size();j++)
		{
			Object data[] = (Object[]) list1.get(j);	
			
			siteName=data[0].toString().toLowerCase();
			//prepare query
			req="select doy, tmstamp, press, temp, total_zen  from rinex where sat=?";
			//execute query
			try {
			       list2 =(List) qRunner.query(conn,
								    		   req,
								    		   siteName,
								    		   new ArrayListHandler());
			} catch (SQLException e2) {
			       e2.printStackTrace();
			}
			//get the result
			for(int k=0;k<list2.size();k++)
			{
				Object data1[] = (Object[]) list2.get(k);
				
				//test on the year
				time=(Timestamp)data1[1];
				if (paramYear==time.getYear()+1900)
				{
					rinexHour=time.getHours();
					rinexDoy=Integer.parseInt(data1[0].toString());					
					if(rinexDoy==paramDoy)
					{
						
						
					//insert into ptu table
					req="INSERT INTO ptu" +
							"(session_id,hr,data0,pres," +
							"temp,site)" +				
							"VALUES (?,?,?,?,?,?)";
					 try {
							inserts = qRunner.update(conn,req,
										MainClass.session,
										rinexHour,
										0,
										Double.parseDouble(data1[2].toString()),
										Double.parseDouble(data1[3].toString()),
										siteName.toUpperCase());						
						} catch (SQLException e) {									
							e.printStackTrace();
						}					
					 
					 //insert into ztd table
					 //prepare query
					 req="INSERT INTO ztd" +
								"(session_id,doy,epok,ztd," +
								"site)" +				
								"VALUES (?,?,?,?,?)";
					 //execute query
					 try {
							inserts = qRunner.update(conn,req,
										MainClass.session,
										rinexDoy,
										rinexHour*120,
										(Double.parseDouble(data1[4].toString()))/1000,
										siteName.toUpperCase());						
						} catch (SQLException e) {									
							e.printStackTrace();
						}
					
					}
				}
			}
		
		}
		
		//test if the insertion is done properly
		 if (inserts==1)
			{
 				System.out.println("Insertion in ztd and ptu done ");
 				inserts=0;
			}
		
		//close connection
		DbUtils.closeQuietly(conn);
	}
	/**
	 * function 
	 * to generate PTU2 data and put it into the database
	 *  @return			void
	*/	
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void generatePTU2()
	{
		
		String siteName="";
	  	List 	list1 = new ArrayList(), 
	  			list2 = new ArrayList();
		
		String req = "";
		int inserts=0;
		
		double 	diff0=0, diff1=0,
				ptuPres0=0,ptuTemp0=0,
				ptuPres1=0,ptuTemp1=0;
		
		double []P=new double[4];
		double []T=new double[4];
	
		//connect to the database
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
		
		//get GPS code list from sites_gps in list1 then in data[]
	  	try {
		       list1 =(List) qRunner.query(conn,
							    		   "select code from sites_gps",
							    		   new ArrayListHandler());
		       
		} catch (SQLException e2) {
		       e2.printStackTrace();
		}
	
		for(int j=0;j<list1.size();j++)
		{
			Object data[] = (Object[]) list1.get(j);
			siteName=data[0].toString().toUpperCase();
			//prepare query
			req="select hr, pres, temp  from ptu where site=?  and session_id= "+MainClass.session;
			//execute query
			try {
			       list2 =(List) qRunner.query(conn,
								    		   req,
								    		   siteName,
								    		   new ArrayListHandler());
			} catch (SQLException e2) {
			       e2.printStackTrace();
			}
			//get the result
			if (list2.size()>0)
			{
				for(int k=0;k<(list2.size())-1;k++)
				{
					//get actual row from ptu
					Object data1[] = (Object[]) list2.get(k);
					//get the next row from ptu
					Object data2[] = (Object[]) list2.get(k+1);
					//get the pres and temp from actual row
					ptuPres0=(Double.parseDouble(data1[1].toString()));
					ptuTemp0=(Double.parseDouble(data1[2].toString()));
					//get the pres and temp from next row
					ptuPres1=(Double.parseDouble(data2[1].toString()));
					ptuTemp1=(Double.parseDouble(data2[2].toString()));
					//calculate diff0 and diff1 to generate ptu2 values
					diff0=(ptuPres1-ptuPres0)/4;
					diff1=(ptuTemp1-ptuTemp0)/4;
					//calcul ptu2 value
					P[0]=ptuPres0;
					T[0]=ptuTemp0;
					for(int i=0;i<3;i++)
					{
						P[i+1]=P[i]+diff0;
						T[i+1]=T[i]+diff1;
					}
					//insert calculated values into database
					for(int i=0;i<4;i++)
					{
						//prepare query
						req="INSERT INTO ptu2" +
								"(session_id,hr,m,temp," +
								"pres,site)" +				
								"VALUES (?,?,?,?,?,?)";
						//execute query
						try {
							inserts = qRunner.update(conn,req,
										MainClass.session,
										Double.parseDouble(data1[0].toString()),
										15*i,
										T[i],
										P[i],
										siteName);						
						} catch (SQLException e) {									
							e.printStackTrace();
						}
					}
					//for the last row, we just duplicate it, no calculation
					if(k==list2.size()-2)
					{
						for(int i=0;i<4;i++)
						{
							//prepare query
							req="INSERT INTO ptu2" +
									"(session_id,hr,m,temp," +
									"pres,site)" +				
									"VALUES (?,?,?,?,?,?)";
							//execute query
							 try {
									inserts = qRunner.update(conn,req,
												MainClass.session,
												(Integer.parseInt(data1[0].toString()))+1,
												15*i,
												ptuTemp1,
												ptuPres1,
												siteName);
							 } catch (SQLException e) {									
									e.printStackTrace();
							 }
						}
					}
				}
			}
			
		}
		//test if the insertion is done properly
		if (inserts==1)
		{
				System.out.println("Insertion in ptu2  done ");
		inserts=0;
		}
		
		//close connection
		DbUtils.closeQuietly(conn);
	}

}
